from typing import List, Optional
from models.category import Category
from utils.database import get_db
from sqlalchemy.orm import Session
from sqlalchemy import text


def get_categories() -> List[Category]:
    """获取所有分类信息
    
    Returns:
        List[Category]: 包含所有分类信息的列表
    """
    db: Session = next(get_db())
    try:
        # 执行SQL查询获取所有分类
        result = db.execute(text("SELECT id, name, description, parent_id, level, sort_order, created_at, updated_at FROM categories WHERE parent_id = 0"))
        categories = [Category(id=row[0], name=row[1], description=row[2], parent_id=row[3], level=row[4], sort_order=row[5], created_at=row[6], updated_at=row[7]) for row in result.fetchall()]
        return categories
    finally:
        db.close()


def get_category_by_id(category_id: int) -> Optional[Category]:
    """根据分类ID获取该分类子集信息
    
    Args:
        category_id (int): 分类的唯一标识ID
        
    Returns:
        Optional[Category]: 如果找到对应ID的分类则返回该分类，否则返回None
    """
    db: Session = next(get_db())
    try:
        # 执行SQL查询获取指定ID的分类
        # 正确的查询语句
        result = db.execute(text("SELECT id, name, description, parent_id, level, sort_order, created_at, updated_at FROM categories WHERE id = :id"), {"id": category_id})
        row = result.fetchone()
        if row:
            return Category(id=row[0], name=row[1], description=row[2], parent_id=row[3], level=row[4], sort_order=row[5], created_at=row[6], updated_at=row[7])
        return None
    finally:
        db.close()


def create_category(category: Category) -> Category:
    """创建新的分类信息
    
    Args:
        category (Category): 要创建的分类信息
        
    Returns:
        Category: 创建成功的分类信息，包含数据库生成的ID
    """
    db: Session = next(get_db())
    try:
        # 执行SQL插入新分类
        db.execute(text("INSERT INTO categories (name, parent_id, level, sort_order, created_at, updated_at) VALUES (:name, :parent_id, :level, :sort_order, :created_at, :updated_at)"), 
                   {"name": category.name, "parent_id": category.parent_id, "level": category.level, "sort_order": category.sort_order, "created_at": category.created_at, "updated_at": category.updated_at})
        db.commit()
        # 获取插入的分类ID
        result = db.execute("SELECT LAST_INSERT_ID()")
        category_id = result.fetchone()[0]
        category.id = category_id
        return category
    finally:
        db.close()


def update_category(category_id: int, category: Category) -> Optional[Category]:
    """更新指定ID的分类信息
    
    Args:
        category_id (int): 要更新的分类ID
        category (Category): 包含更新信息的分类对象
        
    Returns:
        Optional[Category]: 如果成功更新则返回更新后的分类信息，否则返回None
    """
    db: Session = next(get_db())
    try:
        # 执行SQL更新分类
        db.execute(text("UPDATE categories SET name = :name, description = :description,  parent_id = :parent_id, level = :level, sort_order = :sort_order, updated_at = :updated_at WHERE id = :id"),
                   {"id": category_id, "name": category.name, "description": category.description, "parent_id": category.parent_id, "level": category.level, "sort_order": category.sort_order, "updated_at": category.updated_at})
        db.commit()
        # 返回更新后的分类
        updated_category = get_category_by_id(category_id)
        return updated_category
    finally:
        db.close()


def delete_category(category_id: int) -> bool:
    """删除指定ID的分类信息
    
    Args:
        category_id (int): 要删除的分类ID
        
    Returns:
        bool: 如果成功删除则返回True，否则返回False
    """
    db: Session = next(get_db())
    try:
        # 执行SQL删除分类
        result = db.execute(text("DELETE FROM categories WHERE id = :id"), {"id": category_id})
        db.commit()
        return result.rowcount > 0
    finally:
        db.close()


# 在文件末尾添加以下函数
def get_subcategories(parent_id: int) -> List[Category]:
    """根据父级分类ID获取子分类信息
    
    Args:
        parent_id (int): 父级分类ID
        
    Returns:
        List[Category]: 包含指定父级分类下所有子分类信息的列表
    """
    db: Session = next(get_db())
    try:
        # 执行SQL查询获取指定父级ID的子分类
        result = db.execute(text("SELECT id, name, description, parent_id, level, sort_order, created_at, updated_at FROM categories WHERE parent_id = :parent_id"), {"parent_id": parent_id})
        subcategories = [Category(id=row[0], name=row[1], description=row[2], parent_id=row[3], level=row[4], sort_order=row[5], created_at=row[6], updated_at=row[7]) for row in result.fetchall()]
        return subcategories
    finally:
        db.close()